﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.IO;
using System.Net;

namespace DataImporter
{
    class Program
    {
        static void Main(string[] args)
        {
            WebClient webClient = new WebClient();
            webClient.DownloadFile("http://www.magictraders.com/pricelists/current-magic-weekly-excel.txt", @"prices.txt");

            List<Price> priceList = new List<Price>();

            StreamReader reader = new StreamReader("prices.txt");
            try
            {
                do
                {
                    string line = reader.ReadLine();
                    string parsedLine = parseLine(line);
                    string[] fields = parsedLine.Split('|');
                    priceList.Add(new Price() 
                                    { ID = Guid.NewGuid(), 
                                      CardName = fields[0], 
                                      Expansion=fields[1], 
                                      MedianPrice = Convert.ToDecimal(fields[2]),
                                      MeanPrice = Convert.ToDecimal(fields[4]),
                                      HighPrice = Convert.ToDecimal(fields[5]),
                                      LowPrice = Convert.ToDecimal(fields[6]),
                                      LastUpdatedDate = DateTime.Now
                                    });

                }
                while (reader.Peek() != -1);
            }

            catch
            {
                Console.WriteLine("File is empty");
            }

            finally
            {
                reader.Close();
                Console.ReadLine();
            }

            //Make a temp table in sql server that matches our production table
            string tmpTable = "create table #Prices (ID uniqueidentifier, CardName nvarchar(75), Timestamp DateTime)";

            //Create a datatable that matches the temp table exactly.
            DataTable table = new DataTable();
            table.Columns.Add(new DataColumn("ID", typeof(Guid)));
            table.Columns.Add(new DataColumn("CardName", typeof(string)));
            table.Columns.Add(new DataColumn("Expansion", typeof(string)));
            table.Columns.Add(new DataColumn("MedianPrice", typeof(decimal)));
            table.Columns.Add(new DataColumn("MeanPrice", typeof(decimal)));
            table.Columns.Add(new DataColumn("HighPrice", typeof(decimal)));
            table.Columns.Add(new DataColumn("LowPrice", typeof(decimal)));
            table.Columns.Add(new DataColumn("LastUpdatedDate", typeof(DateTime)));

            //Add prices in our list to our DataTable
            foreach (Price price in priceList)
            {
                DataRow row = table.NewRow();
                row["ID"] = price.ID;
                row["CardName"] = price.CardName;
                row["Expansion"] = price.Expansion;
                row["MedianPrice"] = price.MedianPrice;
                row["MeanPrice"] = price.MeanPrice;
                row["HighPrice"] = price.HighPrice;
                row["LowPrice"] = price.LowPrice;
                row["LastUpdatedDate"] = price.LastUpdatedDate;
                table.Rows.Add(row);
            }

            //Connect to DB
            string conString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            using (SqlConnection con = new SqlConnection(conString))
            {
                con.Open();

                //Execute the command to make a temp table
                SqlCommand cmd = new SqlCommand(tmpTable, con);
                cmd.ExecuteNonQuery();
            }
	
        }

        private static string parseLine(string rawLine)
        {
            return rawLine;
        }
    }

    internal class Price
    {
        public Guid ID { get; set; }
        public string CardName { get; set; }
        public string Expansion { get; set; }
        public decimal MedianPrice { get; set; }
        public decimal MeanPrice { get; set; }
        public decimal HighPrice { get; set; }
        public decimal LowPrice { get; set; }
        public DateTime LastUpdatedDate { get; set; }
    }
}
